package com.java.test.system.feesettings;

import com.java.test.system.feesettings.dto.FeeSettingsAddDTO;
import com.java.test.system.feesettings.dto.FeeSettingsQueryDTO;
import com.java.test.system.feesettings.dto.FeeSettingsUpdateDTO;
import com.java.test.system.feesettings.entity.FeeSettingsEntity;
import com.java.test.system.feesettings.entity.FeeTypeEntity;
import com.java.test.system.feesettings.entity.FundsTypeEntity;
import com.java.test.system.feesettings.entity.MemberLVEntity;
import com.java.test.system.registrationtemplate.entity.RegistrationTemplateEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 手续费设置Dao
 *
 * @author lijie1
 * @date 2020/6/30 0030
 */
@Component
public class FeeSettingsDAO {

    private JdbcTemplate jdbcTemplate;
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 查询
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public List<FeeSettingsEntity> getSelectByList(FeeSettingsQueryDTO queryDTO) {
        StringBuffer sql = new StringBuffer("select * from sys_fee_settings where 1=1 ");
        List<Object> parame = new ArrayList<>();
        if(queryDTO.getMemberGradeId()!=null&&!"".equals(queryDTO.getMemberGradeId())){
            sql.append(" and memberGradeId = ? ");
            parame.add(queryDTO.getMemberGradeId());
        }
        sql.append(" order by memberGradeId");
        List<FeeSettingsEntity> list = jdbcTemplate.query(sql.toString(),parame.toArray(),new FeeSettingsEntity());

        return list;
    }

    /**
     * 获取数据BYName
     * @param addDTO
     * @return
     */
    public FeeSettingsEntity getByGName(FeeSettingsAddDTO addDTO) {
        String sql = "select * from sys_fee_settings where name = ? and fee_type = ? and fee_scale = ? and area_id = ? and funds_type = ? and memberGradeId = ?  ";
        FeeSettingsEntity info = null;
        try {
            info= jdbcTemplate.queryForObject(sql, new FeeSettingsEntity(), addDTO.getName(),addDTO.getFeeType(),addDTO.getFeeScale(),addDTO.getAreaId(),addDTO.getFundsType(),addDTO.getMemberGradeId());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return info;
    }

    /**
     * 获取数据BYid
     * @param id
     * @return
     */
    public FeeSettingsEntity getById(String id) {
        String sql = "select *  from sys_fee_settings where id = ?";
        FeeSettingsEntity info = null;
        try {
            info = jdbcTemplate.queryForObject(sql, new FeeSettingsEntity(), id);
        }catch (EmptyResultDataAccessException e){
            return null;
        }

        return info;
    }


    /**
     * 保存
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public void insertSave(FeeSettingsAddDTO addDTO) {
        String sql ="insert into sys_fee_settings(name,fee_type,fee_scale,area_id,funds_type,remarks,operation,createTime,status,memberGradeId) " +
                "values (?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,addDTO.getName(),addDTO.getFeeType(),addDTO.getFeeScale(),addDTO.getAreaId(),
                addDTO.getFundsType(),addDTO.getRemarks(),addDTO.getOperation(),
                addDTO.getCreateTime(),addDTO.getStatus(),addDTO.getMemberGradeId());
    }

    /**
     * 判断当前数据是否存在
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public RegistrationTemplateEntity getByKeyExcludeId(FeeSettingsUpdateDTO updateDTO) {
        String sql = "select * from sys_fee_settings where name = ? and fee_type = ? and fee_scale = ? and area_id = ? and funds_type = ? and memberGradeId = ? and id <> ?";
        RegistrationTemplateEntity info = null;
        try {
            info = jdbcTemplate.queryForObject(sql, new RegistrationTemplateEntity(),updateDTO.getName(),updateDTO.getFeeType(),
                    updateDTO.getFeeScale(),updateDTO.getAreaId(),updateDTO.getFundsType(),updateDTO.getMemberGradeId(),updateDTO.getId());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return info;
    }

    /**
     * 修改
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public void updateById(FeeSettingsUpdateDTO updateDTO) {
        String sql = "update sys_fee_settings " +
                "set  name = ?,fee_type = ?,fee_scale = ?,area_id = ?,funds_type = ?,remarks = ?,modifyTime=?,status=? " +
                "where id = ?";

        jdbcTemplate.update(sql, updateDTO.getName(),updateDTO.getFeeType(),updateDTO.getFeeScale(),updateDTO.getAreaId(),
                updateDTO.getFundsType(),updateDTO.getRemarks(),
                updateDTO.getModifyTime(),updateDTO.getStatus(),updateDTO.getId());
    }

    /**
     * 删除
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public void deleteById(String id) {
        String sql ="DELETE FROM sys_fee_settings WHERE id = ?";
        jdbcTemplate.update(sql,id);
    }

    /**
     * 手续费类型查询
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public List<FeeTypeEntity> getFeeTypeListByIds(long feeType) {
        String sql = "select * from sys_fee_type where id in("+feeType+")";
        List<FeeTypeEntity> list = new ArrayList<FeeTypeEntity>();
        try {
            list= jdbcTemplate.query(sql,new FeeTypeEntity());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return list;
    }

    /**
     * 查询币种
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public List<FundsTypeEntity> getFundsTypeListByIds(long fundsType) {
        String sql = "select * from sys_funds_type where id in("+fundsType+")";
        List<FundsTypeEntity> list = new ArrayList<FundsTypeEntity>();
        try {
            list= jdbcTemplate.query(sql,new FundsTypeEntity());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return list;
    }

    /**
     * 查询手续费类型
     * @author 
     * @date 2020/7/16 0016 17:57 
     * @return java.util.List<com.java.test.system.registrationtemplate.entity.TypeEntity>
     */
    public List<FeeTypeEntity> selectToListByFeeType() {
        String sql = "select * from sys_fee_type ";

        List<FeeTypeEntity> list = jdbcTemplate.query(sql,new FeeTypeEntity());

        return list;
    }

    /**
     * 查询未设置的会员等级
     * @author 
     * @date 2020/10/10 0010 11:01 
     * @return java.util.List<com.java.test.system.feesettings.entity.MemberLVEntity>
     */
    public List<MemberLVEntity> selectToListByLV() {
        String sql = "select * from member_member_grade ";

        List<MemberLVEntity> list = jdbcTemplate.query(sql,new MemberLVEntity());

        return list;
    }

    /**
     * 查询未设置的会员等级
     * @author
     * @date 2020/10/10 0010 11:01
     * @return java.util.List<com.java.test.system.feesettings.entity.MemberLVEntity>
     */
    public List<MemberLVEntity> selectToLVListById(String id) {
        String sql = "    SELECT t.* FROM member_member_grade t " +
                "where t.id not in (select a.memberGradeId from sys_fee_settings a) " +
                "or t.id in(select a.memberGradeId from sys_fee_settings a where a.id = ?)";

        List<MemberLVEntity> list = jdbcTemplate.query(sql,new MemberLVEntity(),id);

        return list;
    }


    /**
     * 查询未设置的会员等级ByIds
     * @author
     * @date 2020/10/10 0010 11:01
     * @return java.util.List<com.java.test.system.feesettings.entity.MemberLVEntity>
     */
    public List<MemberLVEntity> selectToListByLV(String ids) {
        String sql = "select * from member_member_grade where id in("+ids+")";

        List<MemberLVEntity> list = jdbcTemplate.query(sql,new MemberLVEntity());

        return list;
    }

    /**
     * 查询手续费列表By手续费类型
     * @param feeType
     * @author
     * @date 2020/10/10 0010 11:21
     * @return void
     */
    public List<FeeSettingsEntity> selectToListByFeeT(String feeType) {
        String sql = "select * from sys_fee_settings t where t.fee_type=?";

        List<FeeSettingsEntity> list = jdbcTemplate.query(sql,new FeeSettingsEntity(),feeType);

        return list;
    }

    /**
     * 查询
     * @author lijie1
     * @date 2020/6/30 0030
     */
    public List<Map<String, Object>>  getSelectByListOrderBy() {
        String sql = "SELECT IFNULL( a.fee_scale, 0 )*10000 fee_scale FROM member_member_grade t " +
                "LEFT JOIN sys_fee_settings a ON t.id = a.memberGradeId  " +
                "WHERE 1=1 ORDER BY t.member_grade_name";

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        return list;
    }
}
